package Messages;

import ocsf.server.ConnectionToClient;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import Logic.LoginInfo;
import server.SQLConnection;
/**
 * this class implements the  get subscriber lending card
 * @author Ron Shenkerman
 * @author Gabi Revah
 * @author Aviad Almog
 * @author Ivan Onyshchenko
 */
public class MessageLendingCard {
        private ConnectionToClient client ;
        private String returndate,bookid,title,enddate,problemdate,problem,flag;
        private Integer i=0,j=0;
        
        
        /**
         * constractor of this class 
         * @param client
         */
        
        public MessageLendingCard(ConnectionToClient client) {
                super();
                this.client = client;
        }



/**
 * getting the search results from the DB 
 * @param str
 * @throws SQLException
 * @throws IOException
 */

        public void SearchResult(String[] str) throws SQLException, IOException {
                
                try 
                {

                        System.out.println("getting book detail for viewing");
                        Statement stmt,stmt1;
                        SQLConnection conn = new  SQLConnection();
                        stmt = conn.getConnection().createStatement();
                        stmt1 = conn.getConnection().createStatement();
                        
                                        
                                        
                        ResultSet rs1=stmt.executeQuery("SELECT * FROM db1.Lending,db1.bookscopies  WHERE  bookscopies.CopyId=Lending.copyid AND SubscriberId='"+str[1]+"' AND Lend='"+"yes"+"';");
                        ResultSet rs= stmt1.executeQuery("SELECT * FROM db1.Lending,db1.bookscopies  WHERE  bookscopies.CopyId=Lending.copyid AND SubscriberId='"+str[1]+"' AND Lend='"+"yes"+"';");            
                        
                        ArrayList<String> arr=new ArrayList<String>();
                                        
                                        
                                try {
                                        
                                        
                                                        arr.add("LendingCard");
                                                        arr.add(null);
                                                        arr.add(null);
                                        
                                
                                                if (rs1.next())
                                                        {
                                                        
                                                         while (rs.next())
                                                         {
                                                                returndate=(rs.getDate(5)).toString();
                                                                bookid=rs.getString(3);
                                                                title=rs.getString(11);
                                                                System.out.println("id:"+returndate+"  bookid:"+bookid+"  title:"+title+"");
                                                                i=i+1;
                                                        arr.add(bookid);
                                                        arr.add(title);
                                                        arr.add(returndate);    
                                                         }
                                                         }
                                                         ResultSet rs2= stmt1.executeQuery("SELECT * FROM db1.Lending,db1.bookscopies  WHERE  bookscopies.CopyId=Lending.copyid AND SubscriberId='"+str[1]+"' AND Lend='"+"no"+"';");
                                                          
                                                         arr.set(1, i.toString());
                                                         if(rs2.next())
                                                                {
                                                                 rs2.previous();
                                                                 while(rs2.next())
                                                                        {
                                                                        returndate=(rs2.getDate(6)).toString();
                                                                        enddate=(rs2.getDate(5)).toString();
                                                                        title=rs2.getString(11);
                                                                        System.out.println("id:"+returndate+"  bookid:"+bookid+"  title:"+title+"");
                                                                        arr.add(title);
                                                                        arr.add(enddate);
                                                                        arr.add(returndate);    
                                                                        j=j+1;
                                                                        }
                                                                 }
                                                         arr.set(2, j.toString());
                                                        ResultSet rs3= stmt1.executeQuery("SELECT * FROM db1.subscribersproblem WHERE  SubscriberId='"+str[1]+"';");
                                                         if(rs3.next())
                                                                {
                                                                 rs3.previous();
                                                                 while(rs3.next())
                                                                        {
                                                                                
                                                                                if((rs3.getBoolean(4)))
                                                                                        {problem="Lost";}
                                                                                else {problem="Late";}
                                                                                problemdate=(rs3.getDate(6)).toString();
                                                                                bookid=rs3.getString(3);
                                                                                arr.add(bookid);
                                                                                arr.add(problem);
                                                                                arr.add(problemdate);   
                                                                        }
                                                                 }
                                                        client.sendToClient(arr);
                                                        
  
                                        }
                                                catch (SQLException e)
                                                                {       
                                                                e.printStackTrace();
                                                                }
                }
  
                catch (Exception ex )
                {
          ex.printStackTrace();
                }
                
        }
}